package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.model.Class;
import com.model.CurriInfo;
import com.util.DBUtil;
import com.util.Mode;
import com.util.ModelCurriInfo;

public class CurriInfoDao {
	Connection conn;
	PreparedStatement ps;
	ResultSet rs;
	
	public int selectByname(String name) {
		conn=DBUtil.getConn();
		int row=-1;
		try {
			ps=conn.prepareStatement("select count(*) from curriInfo where IsDelete=0 and CurName=?");
			ps.setString(1, name);
			rs=ps.executeQuery();
			if(rs.next()) {
				row=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int insertAll(String name,String pd) {
		conn=DBUtil.getConn();
		int row=-1;
		String sql="";
		if(pd.equals("修改")) {
			sql="update curriInfo set IsDelete=1 where CurName=?";
		}else {
			sql="insert into curriInfo values(null,?,1)";
		}
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, name);
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int updateById(Class c) {
		conn=DBUtil.getConn();
		int row=-1;
		try {
			ps=conn.prepareStatement("update curriInfo set CurName=? where CurId=?");
			ps.setString(1, c.getClassName());
			ps.setInt(2, c.getClassId());
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int deleteById(int id) {
		conn=DBUtil.getConn();
		int row=-1;
		try {
			ps=conn.prepareStatement("update curriInfo set Isdelete=0 where CurId=?");
			ps.setInt(1, id);
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int seelctCount(String name){
		int count=0;
		conn=DBUtil.getConn();
		String sql="select count(*) from curriInfo where isdelete=1";
		if(name!=null&&!"".equals(name)) {
			sql="select count(*) from curriInfo where Isdelete=1 and CurName like'%"+name+"%'";
		}
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return count;
	}
	
	public ModelCurriInfo selectAll(int page,int rows,String name) {
		ModelCurriInfo m=new ModelCurriInfo();
		ArrayList<CurriInfo> list=new ArrayList<CurriInfo>();
		conn=DBUtil.getConn();
		page=(page-1)*rows;
		String sql="select * from curriInfo where Isdelete=1 limit ?,?";
		if(name!=null&&!"".equals(name)) {
			sql="select * from curriInfo where Isdelete=1 and CurName like'%"+name+"%' limit ?,?";
		}
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, page);
			ps.setInt(2, rows);
			rs=ps.executeQuery();
			while(rs.next()) {
				CurriInfo c=new CurriInfo();
				c.setCurId(rs.getInt(1));
				c.setCurName(rs.getString(2));
				list.add(c);
			}
			m.setRows(list);
			m.setTotal(seelctCount(name));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return m;
	}
	
	public ArrayList<CurriInfo> selectAll() {
		ArrayList<CurriInfo> list=new ArrayList<CurriInfo>();
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("select * from curriInfo");
			rs=ps.executeQuery();
			while(rs.next()) {
				CurriInfo c=new CurriInfo();
				c.setCurId(rs.getInt(1));
				c.setCurName(rs.getString(2));
				list.add(c);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}
	
	public int selectIdByName(String name) {
		conn=DBUtil.getConn();
		int id=0;
		try {
			ps=conn.prepareStatement("select CurId from curriInfo where CurName=?");
			ps.setString(1, name);
			rs=ps.executeQuery();
			if(rs.next()) {
				id=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return id;
	}
	
	public String selectNameById(int id) {
		conn=DBUtil.getConn();
		String name="";
		try {
			ps=conn.prepareStatement("select CurName from curriInfo where CurId=?");
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()) {
				id=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return name;
	}
}
